# Generate the final output.
# Format: STU_ID;BOOK_ID1 BOOK_ID2 BOOK_ID3 ...
import time
import sqlite3
import json
import requests
from LogManager import log_output
from tqdm import tqdm

conf = json.load(open('conf.json', 'rt'))
target_server_url = conf['target_url']

time_str = time.strftime("%Y%m%d_%H%M%S", time.localtime())
user_map_path = 'bslen15/user_map.txt'
book_map_path = 'bslen15/bind_book.txt'
data_path = 'bslen15/rank_output.txt'
book_info_path = 'bslen15/book_map.txt'
hot_book_path = 'bslen15/hot_book.txt'
output_dataset = f'datasets/DB_{time_str}.db'


class FinalOutput:
    def __init__(self):
        self.output_dataset = output_dataset

    def execute(self):
        # Create Dataset
        time_str = time.strftime("%Y%m%d_%H%M%S", time.localtime())
        self.output_dataset = f'datasets/DB_{time_str}.db'
        conn = sqlite3.connect(self.output_dataset)
        conn.execute(
            'CREATE TABLE book_info (book_id int primary key, isbn text, code text, title text)')
        conn.execute('CREATE TABLE book_rec (user_id int, book_id int)')
        conn.execute(
            'CREATE TABLE user_info (user_id int primary key, username text)')
        conn.execute(
            'CREATE TABLE name_to_user_id (username text primary key, user_id int)')
        conn.execute(
            'CREATE TABLE hot_book (book_id int primary key, count int)')
        conn.commit()

        print('Reading files...')
        f_user_map = open(user_map_path, 'rt')
        f_book_map = open(book_map_path, 'rt')
        f_book_info = open(book_info_path, 'rt')

        user_map = {}
        book_map = {}

        book_set = set()
        cursor = conn.cursor()
        for line in f_book_info.readlines():
            book_id, isbn, code, title = line.rstrip().split(';')
            if book_id in book_set:
                continue
            title = title.replace('%', '').replace("'", "''")
            cursor.execute(
                f'INSERT INTO book_info VALUES ({book_id},\'{isbn}\',\'{code}\',\'{title}\')')
            book_set.add(book_id)
        conn.commit()

        username_set = set()
        for line in f_user_map.readlines():
            username, user_id = line.strip().split(';')
            cursor.execute(
                f'INSERT INTO user_info VALUES ({user_id},\'{username}\')')
            user_map[user_id] = username
            if username in username_set:
                continue
            username_set.add(username)
            cursor.execute(
                f'INSERT INTO name_to_user_id VALUES (\'{username}\',{user_id})')
        f_user_map.close()
        conn.commit()

        for line in f_book_map.readlines():
            book_id, _, _, code = line.strip().split(';')
            book_map[code] = book_id
        f_book_map.close()

        f_hot_book = open(hot_book_path, 'rt')
        for line in f_hot_book.readlines():
            book_id, count = line.rstrip().split(';')
            cursor.execute(f'INSERT INTO hot_book VALUES ({book_id}, {count})')

        conn.commit()

        print('Writing Recommendation...')
        f_data = open(data_path, 'rt')
        for line in tqdm(f_data.readlines()):
            user_code, _, _, rec_list = line.strip().split(';')
            for rec in rec_list.rstrip().split():
                cursor.execute(
                    f'INSERT INTO book_rec VALUES ({user_code}, {book_map[rec]})')

        f_data.close()
        conn.commit()
        cursor.close()
        conn.close()

        print('Finished!')


if __name__ == '__main__':
    final_output = FinalOutput()
    log_output('输出文件：写入数据库', send_status=True)
    final_output.execute()
    log_output('输出文件：发送数据库', send_status=True)

    # 发送数据库至目标服务器
    dataset_filename = final_output.output_dataset.split('/')[-1]
    files = {'file': open(final_output.output_dataset, 'rb')}
    requests.post(target_server_url+'upload_db', files=files)
    result = json.loads(requests.post(target_server_url+'set_db', json={'db_path': dataset_filename}).text)

    if 'ok' not in result or not result['ok']:
        log_output('输出文件：发送失败', send_status=True)
    else:
        log_output('数据库更新完成', send_status=True)
